package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Properties;
import java.util.logging.Level;
import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.MLocator;
import org.compiere.model.MOrg;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import extend.org.compiere.utils.Util;

public class MM_Form_Z7 extends SvrProcess {
	/*Current context*/
	private Properties m_ctx ;
	
	private ProcessInfo pi ;
	
	Integer p_M_Locator_ID, p_Accountant_ID;
	
	Date p_DateAcctFrom, p_DateAcctTo;
	
	String ResultPath;
	
	/*
	* Prepare - e.g., get Parameters.
	*/
	@Override
	protected void prepare() {
	log.info( "");
	m_ctx = Env. getCtx();
	pi = getProcessInfo();
	ProcessInfoParameter[] para = getParameter();
	for (int i = 0; i < para.length; i++) {
		String name = para[i].getParameterName();
		if (para[i].getParameter() == null)
			;
		else if (name.equals("M_Locator_ID"))
			p_M_Locator_ID = para[i].getParameterAsInt();
		else if (name.equals("Accountant_ID"))
			p_Accountant_ID = para[i].getParameterAsInt();
		else if (name.equals("DateAcct")) {
			p_DateAcctFrom = (Date) para[i].getParameter();
			p_DateAcctTo = (Date) para[i].getParameter_To();
		}
		else
			log.log(Level. SEVERE, "Unknown Parameter: " + name);
		}
	
	}

	@Override
	protected String doIt() throws Exception {
		//File path
		ResultPath = "Form_Z7_";
		Integer pathI = 0;
		boolean flag = true ;
		while (flag) {
			File f = new File(Util.localFilePath + ResultPath + ".xlsx");
			try {
				if (f.exists() && !f.delete()) {
					ResultPath = ResultPath + "(" ;
					ResultPath = ResultPath .substring(0, 9) + (++pathI) + ")";
				} else {
					flag = false;
				}
			} catch (Exception e) {
				e.printStackTrace();
				ResultPath = ResultPath + "(" + (++pathI) + ")";
			}
		}
		ResultPath = Util.localFilePath + ResultPath + ".xlsx" ;
		MAttachmentEntry entry = Util. getAttachment(pi, m_ctx, ResultPath);
		if (entry == null)
			throw new AdempiereException(Msg.translate (m_ctx , "NotFoundTemplate"));

		Workbook wb = new XSSFWorkbook(entry.getInputStream());
		Sheet sheet = wb.getSheetAt(0);
		Row R = null;
		Cell C = null;
		
		//Cell Styling
		CellStyle mergeStyle = wb.createCellStyle();
		mergeStyle.setBorderBottom(( short)1);
		mergeStyle.setBorderTop(( short)1);
		mergeStyle.setBorderLeft(( short)1);
		mergeStyle.setBorderRight(( short)1);
		Font mergeFont = wb.getFontAt(sheet.getRow(9).getCell(3).getCellStyle().getFontIndex());
		mergeStyle.setFont(mergeFont);
		mergeStyle.setWrapText( true);
		mergeStyle.setVerticalAlignment(CellStyle. VERTICAL_CENTER);
		
		//Header
		sheet.getRow(8).getCell(0).setCellValue(MOrg. get(m_ctx, Env.getAD_Org_ID(m_ctx)).getName());
		sheet.getRow(8).getCell(38).setCellValue((new MBPartner(m_ctx , MOrg.get(m_ctx, Env.getAD_Org_ID(m_ctx )).getLinkedC_BPartner_ID(get_TrxName()), get_TrxName()).getFI_BIN_IIN()));
		
		sheet.getRow(11).getCell(3).setCellValue((new MLocator(m_ctx, p_M_Locator_ID, get_TrxName()).getM_Warehouse().getName()));
		
//		sheet.getRow(18).getCell(14).setCellValue((new MHRJob()));
		sheet.getRow(18).getCell(24).setCellValue((new MLocator(m_ctx, p_M_Locator_ID, get_TrxName()).getValue()));
		
		sheet.getRow(25).getCell(4).setCellValue((new SimpleDateFormat("dd.MM.yyyy").format(System.currentTimeMillis())));
		sheet.getRow(25).getCell(9).setCellValue((new SimpleDateFormat("dd.MM.yyy").format(p_DateAcctFrom)) + "-" + (new SimpleDateFormat("dd.MM.yyy").format(p_DateAcctTo)));
//		sheet.getRow(25).getCell(19).setCellValue((new MHRDepartment(m_ctx, HR_Department_ID, get_TrxName())));
		sheet.getRow(25).getCell(34).setCellValue((new MLocator(m_ctx, p_M_Locator_ID, get_TrxName()).getM_Warehouse().getName()));
		
		sheet.getRow(29).getCell(29).setCellValue((new MBPartner(m_ctx, p_Accountant_ID, get_TrxName()).getName())); //TODO nujno napisat proverku na null
		
		sheet.getRow(32).getCell(24).setCellValue("Остаток на " + new SimpleDateFormat("dd.MM.yyyy").format(p_DateAcctTo));
		
		//Positions
		int row = 40, no = 0;
		double colPrice = 0;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String sql = "WITH t1 AS ( " + 
						" SELECT 	toh.Product, " +  
							" toh.Product_Number, " + 
							" toh.UOM, " + 
							" sum(toh.RemainQty) AS RemainQty, " +  
							" sum(toh.RemainAmount) AS RemainAmt, " + 
							" (SELECT sum(DebitQty) FROM MM_Turnover_V tof WHERE tof.M_Locator_ID = toh.M_Locator_ID AND tof.M_Product_ID = toh.M_Product_ID AND tof.DateAcct >= '" + p_DateAcctFrom + "' AND tof.DateAcct <= '" + p_DateAcctTo + "') AS DebitQty, " +  
							" (SELECT sum(DebitAmount) FROM MM_Turnover_V tof WHERE tof.M_Locator_ID = toh.M_Locator_ID AND tof.M_Product_ID = toh.M_Product_ID AND tof.DateAcct >= '" + p_DateAcctFrom + "' AND tof.DateAcct <= '" + p_DateAcctTo + "') AS DebitAmt, " + 
							" (SELECT sum(CreditQty) FROM MM_Turnover_V tof WHERE tof.M_Locator_ID = toh.M_Locator_ID AND tof.M_Product_ID = toh.M_Product_ID AND tof.DateAcct >= '" + p_DateAcctFrom + "' AND tof.DateAcct <= '" + p_DateAcctTo + "') AS CreditQty, " + 
							" (SELECT sum(CreditAmount) FROM MM_Turnover_V tof WHERE tof.M_Locator_ID = toh.M_Locator_ID AND tof.M_Product_ID = toh.M_Product_ID AND tof.DateAcct >= '" + p_DateAcctFrom + "' AND tof.DateAcct <= '" + p_DateAcctTo + "') AS CreditAmt " + 
						" FROM MM_Turnover_V toh " + 
						" WHERE toh.DateAcct >= '01.01.1900' AND toh.DateAcct <= '" + p_DateAcctTo + "' " + 
							" AND toh.M_Locator_ID = " + p_M_Locator_ID + " " + 
						" GROUP BY toh.Product, toh.Product_Number, toh.UOM, toh.M_Locator_ID, toh.M_Product_ID) " + 
						" select * from t1 " + 
						" where RemainQty <> 0";
		
		try {
			ps = DB.prepareStatement(sql.toString(), get_TrxName());
			rs = ps.executeQuery();
			
			while (rs.next()) {
				//Shifting and merging
				sheet.shiftRows(row, row + 1, 1);
				sheet.addMergedRegion(new CellRangeAddress(row, row, 0, 1));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 2, 9));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 10, 13));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 14, 15));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 16, 17));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 18, 20));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 21, 23));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 24, 27));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 28, 31));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 32, 35));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 36, 39));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 40, 43));
				sheet.addMergedRegion(new CellRangeAddress(row, row, 44, 47));
				
				R = sheet.createRow(row);
				for(int j = 0; j < 48; j++){
					C = R.createCell(j);
					C.setCellStyle(mergeStyle);
				}
				
				sheet.getRow(row).getCell(0).setCellValue(++no);
				sheet.getRow(row).getCell(2).setCellValue(rs.getString("Product"));
				sheet.getRow(row).getCell(10).setCellValue(rs.getString("Product_Number"));
				sheet.getRow(row).getCell(14).setCellValue(rs.getString("UOM"));
				
				if (rs.getInt("DebitQty") != 0) 
					colPrice =  rs.getInt("DebitAmt") / rs.getInt("DebitQty");
				else if (rs.getInt("CreditQty") != 0) 
					colPrice = rs.getInt("CreditAmt") / rs.getInt("CreditQty");
				else if (rs.getInt("RemainQty") != 0) 
					colPrice = rs.getInt("RemainAmt") / rs.getInt("RemainQty");
				sheet.getRow(row).getCell(16).setCellValue(rs.getInt("RemainQty"));
				sheet.getRow(row).getCell(18).setCellValue(colPrice);
				
				sheet.getRow(row).getCell(24).setCellValue(rs.getInt("RemainQty"));
				sheet.getRow(row).getCell(28).setCellValue(rs.getInt("RemainAmt"));
				sheet.getRow(row).getCell(32).setCellValue(rs.getInt("DebitQty"));
				sheet.getRow(row).getCell(36).setCellValue(rs.getInt("DebitAmt"));
				sheet.getRow(row).getCell(40).setCellValue(rs.getInt("CreditQty"));
				sheet.getRow(row).getCell(44).setCellValue(rs.getInt("CreditAmt"));

				row++;
			}
		} catch (Exception e) {
			log.log(Level.SEVERE, sql.toString(), e);
		} finally {
			DB.close(rs, ps);
			rs = null;
			ps = null;
		}
		
		//--------------------------------------------------------------------------
		//Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(ResultPath);
		wb.write(fileOut);
		fileOut.close();
		
		//Open the file with the application associated
		Util. launchFile(new File(ResultPath));
		
		return Msg.translate(m_ctx, "Success");
	}
}